# -*- coding: utf-8 -*-
# created by Src - 一路好时光
from email.header import Header

#所有人邮箱
#所有人工资条excel
#读邮箱、读excel

from openpyxl import load_workbook
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

def src_sendmail(sender_email, staff_email,name,content,table_col_html):
    # 创建邮件对象
    msg = MIMEMultipart()
    msg['From'] = Header("大唐人事部",'utf-8')
    msg['To'] = Header("大唐员工",'utf-8')
    msg['Subject'] = Header("大唐建设集团2024-07月工资",'utf-8')

    # 添加邮件正文
    body = f'''
        <h3>{name}，你好：</h3>
        <p>请查收你2024-07-21月的工资条！</p>
        <table border='1'>
            {table_col_html}
            {content}
        </table>
    '''
    msg.attach(MIMEText(body, 'html', 'utf-8'))
    text = msg.as_string()
    server.sendmail(sender_email, staff_email, text)

#加载excel文件  data_only=True把公式结果算出来打印
wb = load_workbook('时氏集团公司-202407月工资.xlsx',data_only=True)
sheet = wb.active

# 登录邮箱
sender_email = 'srchao007@163.com'
receiver_email = 'srchao007@163.com'
password = 'GJAANJNROECTMCEK'
# 连接到SMTP服务器并发送邮件
server = smtplib.SMTP_SSL('smtp.163.com', 465)  # 使用SSL连接
server.login(sender_email, password)

#循环excel内容
count = 0
table_col_nams = []
table_col_html = ''
for row in sheet.iter_rows(min_row=1):
    count+=1
    if(count==1):
        table_col_html += '<thead>'
        for cell in row:
            table_col_nams.append(cell.value)
            #break
            table_col_html+=f"<th>{cell.value}</th>"
        table_col_html +="</thead>"
    else:
        content = "<tr>\r\n"
        for cell in row:
            print(cell.value, end=',')
            content +=f"<td>{cell.value}</td>\r\n"
        content += "</tr>\r\n"
        name = row[2].value
        staff_email = row[1].value
        src_sendmail(sender_email,staff_email, name,content,table_col_html)
        print(f"成功发送工资条到{staff_email}")
        print()
        break

# 序号,邮箱,姓名,部门,基本工资,绩效工资,提成,电脑补贴,电话补贴,社保,请假,公积金,专项应扣除项,累计应缴预缴额度,累计应缴税额,实发,
# 1,srchao007@163.com,时瑞超,总经办,11600,2900,None,100,None,-382.72,None,-720,None,8497.28,254.92,13497.28,
# 2,srchao007@163.com,马云,总经办,14000,None,None,100,None,-382.72,-643.68,-720,None,7353.6,220.61,12353.6,


# 时瑞超，你好：
# 请查收你2024-07-21月的工资条！
#
# 序号	邮箱	姓名	部门	基本工资	绩效工资	提成	电脑补贴	电话补贴	社保	请假	公积金	专项应扣除项	累计应缴预缴额度	累计应缴税额	实发
# 1	srchao007@163.com	时瑞超	总经办	11600	2900	None	100	None	-382.72	None	-720	None	8497.28	254.92	13497.28


